﻿using System;
using System.Data.SqlClient;

namespace InsertProductIntoNorthwind
{
    class InsertProductIntoNorthwind
    {
        private SqlConnection dbCon;

        private void CheckForNullValueAndAddParameter(SqlCommand cmd,SqlParameter sqlParameter,int? value)
        {
            if (value == null) 
            {
                sqlParameter.Value = DBNull.Value;
            }
            cmd.Parameters.Add(sqlParameter);
        }

        private void CheckForNullValueAndAddParameter(SqlCommand cmd, SqlParameter sqlParameter, string value)
        {
            if (value == null)
            {
                sqlParameter.Value = DBNull.Value;
            }
            cmd.Parameters.Add(sqlParameter);
        }

        private void CheckForNullValueAndAddParameter(SqlCommand cmd, SqlParameter sqlParameter, decimal? value)
        {
            if (value == null)
            {
                sqlParameter.Value = DBNull.Value;
            }
            cmd.Parameters.Add(sqlParameter);
        }

        private void CheckForNullValueAndAddParameter(SqlCommand cmd, SqlParameter sqlParameter, short? value)
        {
            if (value == null)
            {
                sqlParameter.Value = DBNull.Value;
            }
            cmd.Parameters.Add(sqlParameter);
        }

        private void ConnectToDb() 
        {
            dbCon = new SqlConnection("Server=.\\SQLEXPRESS; " +
            "Database=Northwind; Integrated Security=true");
            this.dbCon.Open();
        }

        public void InsertProduct(string productName, int? suplierId, int? categorieId,
            string quantityPerUnit, decimal? unitPrice, short? unitsInStock,
            short? unistOnOrder, short? reorderLevel, bool discounted)
        {
            using (dbCon)
            {
                SqlCommand cmdInsert = new SqlCommand("INSERT INTO Products " +
                      "(productName, supplierID, CategoryID, QuantityPerUnit, UnitPrice, UnitsInStock, "+
                      "UnitsOnOrder, ReorderLevel, Discontinued) VALUES " +
                      "(@productName, @supplierID, @CategoryID, @QuantityPerUnit, @UnitPrice, "+
                      "@UnitsInStock, @UnitsOnOrder, @ReorderLevel, @Discontinued)", dbCon);

                cmdInsert.Parameters.Add(new SqlParameter("@ProductName", productName));
                SqlParameter parameterSuplierId = new SqlParameter("@SupplierID", suplierId);
                SqlParameter parameterCategoryId = new SqlParameter("@CategoryID", categorieId);
                SqlParameter parameterQuantityPerUnit = new SqlParameter("@QuantityPerUnit", quantityPerUnit);
                SqlParameter parameterUnitPrice = new SqlParameter("@UnitPrice", unitPrice);
                SqlParameter parameterUnitsInStock = new SqlParameter("@UnitsInStock", unitsInStock);
                SqlParameter parameterUnitsOnOrder = new SqlParameter("@UnitsOnOrder", unistOnOrder);
                SqlParameter parameterReorderLevel = new SqlParameter("@ReorderLevel", reorderLevel);
                CheckForNullValueAndAddParameter(cmdInsert, parameterSuplierId, suplierId);
                CheckForNullValueAndAddParameter(cmdInsert, parameterCategoryId, categorieId);
                CheckForNullValueAndAddParameter(cmdInsert, parameterQuantityPerUnit, quantityPerUnit);
                CheckForNullValueAndAddParameter(cmdInsert, parameterUnitPrice, unitPrice);
                CheckForNullValueAndAddParameter(cmdInsert, parameterUnitsInStock, unitsInStock);
                CheckForNullValueAndAddParameter(cmdInsert, parameterUnitsOnOrder, unistOnOrder);
                CheckForNullValueAndAddParameter(cmdInsert, parameterReorderLevel, reorderLevel);
                InserParameterDiscounted(discounted, cmdInsert);
                cmdInsert.ExecuteNonQuery();

            }
        }
  
        private void InserParameterDiscounted(bool discounted, SqlCommand cmdInsert)
        {
            if (discounted)
            {
                cmdInsert.Parameters.Add(new SqlParameter("@Discontinued", true));
            }
            else
            {
                cmdInsert.Parameters.Add(new SqlParameter("@Discontinued", false));
            }
        }

        static void Main(string[] args)
        {
            InsertProductIntoNorthwind example = new InsertProductIntoNorthwind();
            example.ConnectToDb();
            example.InsertProduct("pesho", 1, 1, "12 - 550 ml bottles",
                10.00M, (short)100, (short)10, (short)25, false);
        }
    }
}
